﻿Imports Microsoft.VisualBasic
Imports Microsoft.VisualBasic.CompilerServices
Imports System
Imports System.Data.OleDb
Imports Microsoft.Office.Interop.Excel

Namespace ReportExcel
    Public Class clsFormula
        ' Methods
        Public Sub New(ByVal c As Range, ByVal strFormula As String)
            Me.intCol = c.Column
            Me.intRow = c.Row
            Me.vtLoc = New Collection
            Me.vtFieldList = New Collection
            Me.parseFormula(strFormula)
        End Sub

        Public Function getC() As Integer
            Return Me.intCol
        End Function

        Public Function GetFormula1(ByVal rs As OleDbDataReader) As String
            Dim str4 As String = Me.strFormula1
            Dim num As Integer = Me.vtFieldList.Count
            If ((StringType.StrCmp(str4, "", False) <> 0) And (num > 0)) Then
                Dim num3 As Integer = num
                Dim i As Integer = 1
                Do While (i <= num3)
                    Dim str2 As String = StringType.FromObject(Me.vtFieldList.Item(i))
                    Dim str3 As String = StringType.FromObject(ObjectType.StrCatObj("", rs.GetValue(IntegerType.FromString(str2))))
                    str4 = Strings.Replace(str4, ("${" & str2 & "}"), str3, 1, -1, 0)
                    i += 1
                Loop
            End If
            Return str4
        End Function

        Public Function GetFormula2(ByVal intRow As Integer) As String
            Dim collection As New Collection
            collection.Add(("" & StringType.FromInteger(intRow)), "i", Nothing, Nothing)
            Dim str2 As String = ""
            Dim intLoc As Integer = -1
            Dim num4 As Integer = Me.vtLoc.Count
            Dim i As Integer = 1
            Do While (i <= num4)
                Dim num2 As Integer = (intLoc + 2)
                Dim loc As clsLoc = DirectCast(Me.vtLoc.Item(i), clsLoc)
                intLoc = loc.intLoc
                str2 = StringType.FromObject(ObjectType.StrCatObj((str2 & Strings.Mid(Me.strFormula, num2, (intLoc - num2))), collection.Item(loc.strKey)))
                i += 1
            Loop
            str2 = (str2 & Strings.Mid(Me.strFormula, (intLoc + 2)))
            collection.Remove(1)
            Return str2
        End Function

        Public Function GetFormula3(ByVal intRow1 As Integer, ByVal intRow2 As Integer) As String
            Dim str2 As String
            Dim collection As New Collection
            collection.Add(("" & StringType.FromInteger((intRow1 - 1))), "i", Nothing, Nothing)
            collection.Add(("" & StringType.FromInteger(intRow1)), "j", Nothing, Nothing)
            collection.Add(("" & StringType.FromInteger(intRow2)), "k", Nothing, Nothing)
            Dim intLoc As Integer = -1
            Dim num4 As Integer = Me.vtLoc.Count
            Dim i As Integer = 1
            Do While (i <= num4)
                Dim num2 As Integer = (intLoc + 2)
                Dim loc As clsLoc = DirectCast(Me.vtLoc.Item(i), clsLoc)
                intLoc = loc.intLoc
                str2 = StringType.FromObject(ObjectType.StrCatObj((str2 & Strings.Mid(Me.strFormula, num2, (intLoc - num2))), collection.Item(loc.strKey)))
                i += 1
            Loop
            str2 = (str2 & Strings.Mid(Me.strFormula, (intLoc + 2)))
            collection.Remove(3)
            collection.Remove(2)
            collection.Remove(1)
            Return str2
        End Function

        Public Function GetFormula4(ByVal intRow As Integer, ByVal intRow1 As Integer, ByVal intRow2 As Integer) As String
            Dim str2 As String
            Dim collection As New Collection
            collection.Add(("" & StringType.FromInteger(intRow)), "i", Nothing, Nothing)
            collection.Add(("" & StringType.FromInteger(intRow1)), "j", Nothing, Nothing)
            collection.Add(("" & StringType.FromInteger(intRow2)), "k", Nothing, Nothing)
            Dim intLoc As Integer = -1
            Dim num4 As Integer = Me.vtLoc.Count
            Dim i As Integer = 1
            Do While (i <= num4)
                Dim num2 As Integer = (intLoc + 2)
                Dim loc As clsLoc = DirectCast(Me.vtLoc.Item(i), clsLoc)
                intLoc = loc.intLoc
                str2 = StringType.FromObject(ObjectType.StrCatObj((str2 & Strings.Mid(Me.strFormula, num2, (intLoc - num2))), collection.Item(loc.strKey)))
                i += 1
            Loop
            str2 = (str2 & Strings.Mid(Me.strFormula, (intLoc + 2)))
            collection.Remove(3)
            collection.Remove(2)
            collection.Remove(1)
            Return str2
        End Function

        Private Sub parseFormula(ByVal strFormula As String)
            Do While (1 <> 0)
                Dim num4 As Integer
                Dim num3 As Integer = Strings.InStr((num4 + 1), strFormula, "${", 0)
                If (num3 = 0) Then
                    Exit Do
                End If
                num4 = Strings.InStr(num3, strFormula, "}", 0)
                If (num4 = 0) Then
                    Exit Do
                End If
                Dim str4 As String = Strings.Mid(strFormula, (num3 + 2), ((num4 - num3) - 2))
                Me.vtFieldList.Add(str4, Nothing, Nothing, Nothing)
            Loop
            If (Me.vtFieldList.Count <> 0) Then
                Me.strFormula1 = strFormula
                strFormula = ""
            Else
                Dim str2 As String
                Dim num As Integer
                Dim str5 As String
                Dim num6 As Integer = Strings.Len(strFormula)
                num = 1
                Do While (num <= num6)
                    Dim strEval As String = Strings.Mid(strFormula, num, 1)
                    If Module1.IsLetter(strEval) Then
                        num += 1
                        str5 = ""
                        str2 = Strings.Mid(strFormula, num, 1)
                        If Information.IsNumeric(str2) Then
                            GoTo Label_00D3
                        End If
                    End If
                    GoTo Label_010B
Label_00B3:
                    str5 = (str5 & str2)
                    num += 1
                    If (num <= Strings.Len(strFormula)) Then
                        str2 = Strings.Mid(strFormula, num, 1)
                    End If
Label_00D3:
                    If (Information.IsNumeric(str2) And (num <= Strings.Len(strFormula))) Then
                        GoTo Label_00B3
                    End If
                    strFormula = Strings.Replace(strFormula, ("" & strEval & str5), (strEval & "#i"), 1, -1, 0)
Label_010B:
                    num += 1
                Loop
                num = Strings.InStr(1, strFormula, "$", 0)
                Do While (num <> 0)
                    Dim str3 As String = Strings.Mid(strFormula, (num - 1), 1)
                    Dim num2 As Integer = (num + 1)
                    str5 = ""
                    str2 = Strings.Mid(strFormula, num2, 1)
                    Do While (Information.IsNumeric(str2) And (num2 <= Strings.Len(strFormula)))
                        str5 = (str5 & str2)
                        num2 += 1
                        If (num2 <= Strings.Len(strFormula)) Then
                            str2 = Strings.Mid(strFormula, num2, 1)
                        End If
                    Loop
                    strFormula = Strings.Replace(strFormula, (str3 & "$" & str5), (str3 & "#j:" & str3 & "#k"), 1, -1, 0)
                    num = Strings.InStr(1, strFormula, "$", 0)
                Loop
                Dim num5 As Integer = Strings.Len(strFormula)
                num = 1
                Do While (num <= num5)
                    Dim loc As clsLoc
                    If (StringType.StrCmp(Strings.Mid(strFormula, num, 2), "#i", False) = 0) Then
                        loc = New clsLoc("i", num)
                        Me.vtLoc.Add(loc, Nothing, Nothing, Nothing)
                    ElseIf (StringType.StrCmp(Strings.Mid(strFormula, num, 2), "#j", False) = 0) Then
                        loc = New clsLoc("j", num)
                        Me.vtLoc.Add(loc, Nothing, Nothing, Nothing)
                    ElseIf (StringType.StrCmp(Strings.Mid(strFormula, num, 2), "#k", False) = 0) Then
                        loc = New clsLoc("k", num)
                        Me.vtLoc.Add(loc, Nothing, Nothing, Nothing)
                    End If
                    num += 1
                Loop
            End If
            Me.setFormula(strFormula)
        End Sub

        Private Sub setFormula(ByVal strFormula1 As String)
            Me.strFormula = strFormula1
        End Sub


        ' Fields
        Private intCol As Integer
        Private intRow As Integer
        Private strFormula As String
        Private strFormula1 As String
        Private vtFieldList As Collection
        Private vtLoc As Collection
    End Class
End Namespace

